package com.pk.platform.business.report.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.pk.platform.business.charge.dao.IChargeIndexDao;
import com.pk.platform.business.core.dao.impl.GenericDaoImpl;
import com.pk.platform.business.report.dao.ITuitionReportDao;
import com.pk.platform.business.report.vo.TuitionReportVO;
import com.pk.platform.domain.charge.ChargeIndex;
import com.pk.platform.util.StringConverter;
import com.pk.platform.util.page.ListPage;
import com.pk.platform.util.page.Pager;

public class TuitionReportDaoImpl extends GenericDaoImpl implements ITuitionReportDao{

	private IChargeIndexDao chargeIndexDao;
	
	public ListPage getTuitionReportList(TuitionReportVO t, Pager pager,int showType)
	{
		return this.getListPage(getReportSql(t, showType), null, pager);
	}
	public List<Map<String, Object>> getYears(){
		String  sql = "SELECT distinct substr(period,1,4) period FROM calendar";
		return this.getSjt().queryForList(sql.toString());
	}
	public List<Map<String, Object>> getPeriodAndCharge(String year){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT period FROM calendar where period like '"+year+"%' ");
		sql.append("union all ");
		sql.append("select * from (select indexName from charge_index ci order by ci.sort) t");
		return this.getSjt().queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> getCharge(){
		StringBuffer sql = new StringBuffer();
		sql.append("select indexName from charge_index order by sort");
		return this.getSjt().queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> getReportList(TuitionReportVO t,int showType){
		return this.getSjt().queryForList(getReportSql(t,showType));
	}
	
	public List<Map<String, Object>> getReportTotalList(TuitionReportVO t,int showType){
		int periodStart = Integer.parseInt(t.getPeriodStart());
		List<Map<String, Object>> pac = getPeriodAndCharge(periodStart + "");
		Map map; 
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(y.sumAmount) as sumAmount ");
		for (int s = 0; s < pac.size(); s++) {
			StringBuffer byname = new StringBuffer();
			map = new HashMap();
			map = pac.get(s);
			if(s<12)
				byname.append("column").append(s + 1);
			else
				byname.append(map.get("period").toString());
			sql.append(",sum(y.").append(byname).append(") as '").append(byname).append("' ");
		}
		sql.append(" from(");
		sql.append(getReportSql(t,showType));
		sql.append(" ) y ");
		return this.getSjt().queryForList(sql.toString());
	}
	
	private String getReportSql(TuitionReportVO t, int showType)
	{
		String amountField = "amount";
		if(showType == 1)
		{
			amountField = "modifyAmount";
		}
		List<ChargeIndex> indexList = chargeIndexDao.queryAllChargeIndexList();
		int indexLen = indexList.size();
		StringBuffer sql = new StringBuffer();
		sql.append("select cl.classtype, ch.childnum, ch.childname, cl.classname, t6.*,");
		sql.append(" (t6.column1 + t6.column2 + t6.column3 + t6.column4 + t6.column5 + t6.column6");
		sql.append(" + t6.column7 + t6.column8 + t6.column9 + t6.column10 + t6.column11 + t6.column12) sumAmount");
		sql.append(" from child ch left join cla cl on ch.classId = cl.id left join");
		sql.append(" (select t4.childId, t4.year,");
		sql.append(" sum(case when t4.month = '01' then round(t4.").append(amountField).append(", 0) else 0 end) column1,");
		sql.append(" sum(case when t4.month = '02' then round(t4.").append(amountField).append(", 0) else 0 end) column2,");
		sql.append(" sum(case when t4.month = '03' then round(t4.").append(amountField).append(", 0) else 0 end) column3,");
		sql.append(" sum(case when t4.month = '04' then round(t4.").append(amountField).append(", 0) else 0 end) column4,");
		sql.append(" sum(case when t4.month = '05' then round(t4.").append(amountField).append(", 0) else 0 end) column5,");
		sql.append(" sum(case when t4.month = '06' then round(t4.").append(amountField).append(", 0) else 0 end) column6,");
		sql.append(" sum(case when t4.month = '07' then round(t4.").append(amountField).append(", 0) else 0 end) column7,");
		sql.append(" sum(case when t4.month = '08' then round(t4.").append(amountField).append(", 0) else 0 end) column8,");
		sql.append(" sum(case when t4.month = '09' then round(t4.").append(amountField).append(", 0) else 0 end) column9,");
		sql.append(" sum(case when t4.month = '10' then round(t4.").append(amountField).append(", 0) else 0 end) column10,");
		sql.append(" sum(case when t4.month = '11' then round(t4.").append(amountField).append(", 0) else 0 end) column11,");
		sql.append(" sum(case when t4.month = '12' then round(t4.").append(amountField).append(", 0) else 0 end) column12");
		if(indexLen > 0)
		{
			sql.append(",");
		}
		StringBuffer indexSql = new StringBuffer();
		for(int i = 0; i < indexLen; i++)
		{
			ChargeIndex ci = indexList.get(i);
			indexSql.append(" sum(case when t4.indexName = '").append(ci.getIndexName()).append("' then round(t4.").append(amountField).append(", 0) else 0 end) ").append(ci.getIndexName());
			if(i != indexLen - 1)
			{
				indexSql.append(",");
			}
		}
		
		sql.append(indexSql.toString());
		sql.append(" from (select t1.childId, t2.year, t2.month, t2.amount, t2.modifyAmount, t3.indexName from");
		sql.append(" tuition t1, tuition_detail t2, charge_index t3");
		sql.append(" where t1.id = t2.tuitionId and t2.chargeIndexId = t3.id and t1.isDel = 0");
		sql.append(" ) t4 group by t4.childId, t4.year");
		sql.append(" ) t6 on ch.id = t6.childId where 1 = 1");
		
		if(StringConverter.isNotNull(t.getKindergartenId()))
			sql.append(" and ch.kgId = '").append(t.getKindergartenId()).append("'");
		if(StringConverter.isNotNull(t.getClassId()))
			sql.append(" and cl.id = '").append(t.getClassId()).append("'");
		if(StringConverter.isNotNull(t.getChildName()))
			sql.append(" and ch.childname like '%").append(t.getChildName()).append("%'");
		if(StringConverter.isNotNull(t.getChildNo()))
			sql.append(" and ch.childnum like '%").append(t.getChildNo()).append("%'");
		if(StringConverter.isNotNull(t.getPeriodStart()))
			sql.append( "and t6.year >= '").append(t.getPeriodStart()).append("'");
		if(StringConverter.isNotNull(t.getPeriodEnd()))
			sql.append( "and t6.year <= '").append(t.getPeriodEnd()).append("'");
		
		sql.append(" order by cl.classType, cl.id, ch.childNum");
		return sql.toString();
	}
	
	
	public IChargeIndexDao getChargeIndexDao() {
		return chargeIndexDao;
	}
	
	public void setChargeIndexDao(IChargeIndexDao chargeIndexDao) {
		this.chargeIndexDao = chargeIndexDao;
	}

}
